PostgreSQL SQL优化 LIKE无法使用索引

#PostgreSQL #SQL优化 #LIKE无法使用索引

1 背景知识

1、 LIKE 语句使用后缀通配符 % 不走索引所造成的性能问题。
2、 LIKE 语句不走索引，是和数据库排序字符集（Collate）相关。
3、本文介绍使用LIKE 语句时的调优手段。

2 环境准备

2.1 创建表和索引

创建 t01 表，并在 relname 字段创建 btree 索引。

CREATE TABLE t01(id integer,relname text);
INSERT INTO t01 SELECT oid,relname FROM pg_class;
CREATE INDEX idx_t01_relname ON t01(relname);

2.2 查询语句的执行计划

使用 LIKE 操作符，查询出t01 开头的数据。

EXPLAIN ANALYZE SELECT * FROM t01 WHERE relname LIKE 't01%';
//屏幕输出：
 QUERY PLAN                                          
-------------------------
 Seq Scan on t01  (cost=0.00..8.20 rows=2 width=36) (actual time=0.015..0.055 rows=1 loops=1)
   Filter: (relname ~~ 't01%'::text)
   Rows Removed by Filter: 415
 Planning Time: 0.182 ms
 Execution Time: 0.069 ms
(5 rows)

Note

这里可以看出执行计划并不走索引。

3 SQL 调优1: 修改排序字符集

3.1 指定字段的排序字符集

1、当数据库的 Collate 为 C 时，Like 语句使用后缀通配符（%）时，才会用到索引。
2、修改 t01 表的 relname 字段的排序字符集 COLLATE 为 C。

ALTER TABLE t01 ALTER relname type text COLLATE "C";

3.2 查看语句的执行计划

使用 LIKE 操作符，查询出t01 开头的数据。

EXPLAIN ANALYZE SELECT * FROM t01 WHERE relname LIKE 't01%';
 QUERY PLAN                                                       
---------------------------------------------
 Bitmap Heap Scan on t01  (cost=4.29..7.42 rows=2 width=36) (actual time=0.028..0.029 rows=1 loops=1)
   Filter: (relname ~~ 't01%'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_t01_relname  (cost=0.00..4.29 rows=2 width=0) (actual time=0.019..0.019 rows=1 loops=1)
         Index Cond: ((relname ~>=~ 't01'::text) AND (relname ~<~ 't02'::text))
 Planning Time: 0.201 ms
 Execution Time: 0.050 ms
(7 rows)

Warning

这里可以看出 LIKE 语句是否走索引与排序字符集 COLLATE 是相关的。

4 SQL 调优2: 创建索引指定排序字符集

4.1 指定字段的排序字符集

将排序字符集改回来：修改 relanme 字段的排序字符集修改为 zh_CN.utf8。

ALTER TABLE t01 ALTER relname type text COLLATE "zh_CN.utf8";

4.2 创建索引时指定排序字符集

创建 idx_t01_relname 索引，并指定 collate "C" 选项。

DROP INDEX idx_t01_relname;
CREATE INDEX idx_t01_relname ON t01(relname collate "C");

4.3 查看语句的执行计划

使用 LIKE 操作符，查询出t01 开头的数据。

EXPLAIN ANALYZE SELECT * FROM t01 WHERE relname LIKE 't01%';
//屏幕输出：
QUERY PLAN                                                       
-------------------------------------------
 Bitmap Heap Scan on t01  (cost=4.29..7.42 rows=2 width=36) (actual time=0.092..0.095 rows=1 loops=1)
   Filter: (relname ~~ 't01%'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_t01_relname  (cost=0.00..4.29 rows=2 width=0) (actual time=0.044..0.044 rows=1 loops=1)
         Index Cond: ((relname >= 't01'::text) AND (relname < 't02'::text))
 Planning Time: 0.394 ms
 Execution Time: 0.132 ms
(7 rows)

Note

这里可以看到，LIKE 语句将会执行 Bitmap Index Scan 。

5 SQL调优3: 使用索引操作符扩

关于 PostgreSQL SQL优化索引操作符详细使用和介绍，这里不展开说明，更多内容请参考 PostgreSQL SQL优化索引操作符。

5.1 创建索引时指定字段类型的操作符

DROP INDEX idx_t01_relname;
CREATE INDEX IDX_T01_RELNAME ON t01(relname text_pattern_ops);

操作符	说明
text_pattern_ops	text 类型上的B树索引。
varchar_pattern_ops	varchar 类型上的B树索引。
bpchar_pattern_ops	char 类型上的B树索引。

5.2 查看语句的执行计划

使用 LIKE 操作符，查询出t01 开头的数据。

EXPLAIN ANALYZE SELECT * FROM t01 WHERE relname LIKE 't01%';
//屏幕输出：
QUERY PLAN                                                       
--------------------------------------------
 Bitmap Heap Scan on t01  (cost=4.29..7.42 rows=2 width=36) (actual time=0.048..0.049 rows=1 loops=1)
   Filter: (relname ~~ 't01%'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_t01_relname  (cost=0.00..4.29 rows=2 width=0) (actual time=0.039..0.039 rows=1 loops=1)
         Index Cond: ((relname ~>=~ 't01'::text) AND (relname ~<~ 't02'::text))
 Planning Time: 0.253 ms
 Execution Time: 0.068 ms

6 SQL调优4: 使用 pg_trgm 扩展

6.1 pg_trgm 扩展安装

CREATE EXTENSION pg_trgm;

关于 pg_trgm 扩展详细使用和介绍，这里展开说明，更多内容请参考 PostgreSQL pg_trgm 。

Note

使用了 pg_trgm 扩展，将字段数据进行中文分词，通过这种方式建立起来的索引才能够提高查询性能。

6.2 创建索引时指定字段类型的操作符

这里制定的是 pg_trgm 扩展提供的索引。

DROP INDEX idx_t01_relname;
CREATE INDEX idx_t01_relname ON t01 USING GIST (relname gist_trgm_ops);

6.3 查看语句的执行计划

使用 LIKE 操作符，查询出t01 开头的数据。开销值已经下降。

EXPLAIN ANALYZE SELECT * FROM t01 WHERE relname LIKE 't01%';
//屏幕输出：
QUERY PLAN                                                       
----------------------------------------------
 Bitmap Heap Scan on t01  (cost=4.18..8.24 rows=5 width=23) (actual time=0.080..0.081 rows=1 loops=1)
   Recheck Cond: (relname ~~ 't01%'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_t01_relname  (cost=0.00..4.18 rows=5 width=0) (actual time=0.072..0.073 rows=1 loops=1)
         Index Cond: (relname ~~ 't01%'::text)
 Planning Time: 0.172 ms
 Execution Time: 0.109 ms
(7 rows)